USE [bmig_mensual]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0012_CANTIDAD]    Script Date: 11/28/2011 11:10:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0001_Desistidos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0001_Desistidos]
GO

USE [bmig_mensual]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0001_Desistidos]    Script Date: 11/28/2011 11:10:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WASP_M0001_Desistidos]
 
AS
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WASP_D0001_Desistidos]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AS_Desistidos]

select a.Hsucor, a.hsucur,a.Hcmod,a.Htran,a.Hnrel,a.hcord,a.Hrubro,a.Hcta,a.Hoper, 
	 x = (select min(b.husing)  
from [dbo].[fsh015] b
where a.hsucor=b.hsucor 
	AND B.Htran between 10 and 130
	and b.Hcmod in (530,532)
	and a.Hnrel=b.Hnrel 
	and a.Hsucor=b.Hsucor
	and b.Hfcon between'20060220' and getdate() ),
	y  = (select min(b.huscnf) from [dbo].[fsh015] b
	where a.hsucor=b.hsucor 
	AND B.Htran between 10 and 130
	and b.Hcmod in (530,532)
	and a.Hnrel=b.Hnrel 
	and a.Hsucor=b.Hsucor
	and b.Hfcon between'20060220' and getdate()),
	a.Hcsubo,a.Hcimp1,a.Hcodmo,a.Hfcon
into dbo.AS_Desistidos
 from [fsh016] a 
 where a.Hcmod in (530,532 ) 
       AND A.Htran between 10 and 130 
       and a.Hmodul in (30,32)
       and a.Hfcon between '20060220' and getdate() 
       AND a.hcimp1<>0
 group by a.Hsucor, a.hsucur,a.Hcmod,a.Htran,a.Hnrel,a.hcord,a.Hrubro,
	  a.Hcta,a.Hoper, a.Hcsubo,a.Hcimp1,a.Hcodmo,a.Hfcon
 order by a.hfcon, a.hsucor,a.hcmod,a.htran,a.hnrel,a.hcord 


 CREATE  INDEX [IX_AS_Desistidos] ON [dbo].[AS_Desistidos]([Hoper], [Hcta], [hsucur]) ON [PRIMARY]

END



GO


